<?php
namespace app\admin\controller;

use app\admin\model\Sn as SnModel;
use app\admin\model\DeviceModel;
use app\admin\model\AgentGroup as AgentGroupModel;
use think\Db;
use think\Request;
use think\Csv;
/*use PHPExcel_IOFactory;
use PHPExcel_Cell;
use PHPExcel;*/

class Sn extends Base
{
    public function index()
    {
        $model = new SnModel;
        $DmModel = new DeviceModel;
        $FModel = new AgentGroupModel;

        //渲染页面模板
        $model->field('s.id,s.sn,s.imei,s.status,s.sync_at,s.created_at,s.updated_at,m.name as mname, f.name as fname')
            ->alias('s')
            ->join('device_model m','s.device_model_id = m.id ','left')
            ->join('franchiser_group f','s.franchiser_group_id = f.id','left')
            ->order('s.id','desc');
        //->select();
        // ->paginate(5);

        //搜索名称
        $phname = $this->request->param("phname");

        if(!empty($phname)){
            $model->where('s.sn','like', '%'.$phname.'%');
        }
        $result = $model->paginate(10,false,[
            'query'=>['phname'=>$phname]
        ]);

        //渲染用户组
        $dmodel = $DmModel->field('id,name')->select();
        //渲染代理商
        $fgroup = $FModel->field('id,name')->select();

        //计算总共记录条数
        $count = $result->total();
        //分页
        $page = $result->render();

        //接收ajax传递的值
        $request = request();
        $ispost = $request->isPost();
        if($ispost){
            $id = $_POST["viewId"];
            $view = $model->getAll()->where("id",$id)->find();
            return json([
                'code' => 200,
                'view'=>$view,
            ]);
        }


        $this->assign('currentPage',$result->currentPage());
        $this->assign('lastPage',$result->lastPage());
        $this->assign('page',$page);
        $this->assign('dmodel',$dmodel);
        $this->assign('fgroup',$fgroup);
        $this->assign('sn',$result);
        $this->assign('count',$count);
        $this->assign('empty',"暂无数据");
        return $this->fetch();
    }

    //新增数据
    public function create(){
        $model = new SnModel;
        //接收ajax传递的值
        $ispost = $this->request->isPost();
        if($ispost){
            $formdata = $this->request->post();
            $username = Db::query("select id,sn from ". $this->dbTablePrefix ."sn where `sn`='".$formdata['sn']."' and deleted_at is NULL ");

            if(!empty($username)){
                return json([
                    'code' => 4000,
                    'message'=>"用户名已存在"
                ]);
            }
            $result = $model->allowField(true)->save($formdata);

            if($result == 1){
                return json([
                    'code' => 200,
                ]);
            }

        }
    }

    //修改数据处理
    public function edit($id)
    {
        $model = new SnModel;
        //接收ajax传递的值
        $ispost = $this->request->isPost();

        if($ispost){
            $formdata = $this->request->post();

            $result = $model->allowField(true)->save($formdata,['id'=>$id]);

            if($result == 1){
                return json([
                    'code' => 200,
                ]);
            }
        }
    }

    //删除单条记录
    public function delete($id)
    {
        $result = SnModel::update(['deleted_at'=>date('Y-m-d H:i:s')],['id'=>$id]);//软删除

        if($result){
            return json([
                'code' => 200,
            ]);
        }

    }

    //批量删除
    public function deleteArr()
    {
        $ispost = $this->request->isPost();
        if ($ispost) {
            $idArr = $this->request->post();

            $result = SnModel::where('id','exp','in('.implode(',',$idArr['id']).')')->update(['deleted_at'=>date('Y-m-d H:i:s')]);//软删除

            if($result){
                return json([
                    'code' => 200,
                ]);
            }

        }
    }

    /**
     * 导入csv数据表格 第一行必须要写字段名
     */
    public function importCsv(){
        // 获取表单上传文件
        $file = request()->file('fileUp');

        if(empty($file)) {
            return json([
                'code' => 4001,
            ]);
        }
        // 移动到框架应用根目录/public/uploads/ 目录下
        $info = $file->move(ROOT_PATH.'public/static/upload/csv/');
        //获取文件（日期/文件），$info->getFilename();
        $filename = ROOT_PATH.'public/static/upload/csv/'.$info->getSaveName();
        $handle = fopen($filename,'r');
        $csv = new Csv();
        $result = $csv->input_csv($handle); // 解析csv
        $len_result = count($result);
        if($len_result == 0){
            $this->error('此文件中没有数据！');
        }
        $data_values = '';

        for($i = 1;$i < $len_result+1;$i ++) { // 循环获取各字段值
            $arr = array_values($result[$i]);

            $sn = iconv('gb2312','utf-8', $arr[0] ); // 中文转码
            $imei = iconv('gb2312','utf-8',$arr[1]);
            //$to_id = $arr[4];
            //$te_id = 1;
            $data_values .= "('$sn','$imei'),";
        }
        $data_values = substr($data_values,0,- 1 ); // 去掉最后一个逗号

        fclose($handle); // 关闭指针
        // 批量插入数据表中
        $result = Db::execute("insert into ". $this->dbTablePrefix ."sn (sn,imei) values $data_values" );
        if($result){
            return json([
                'code' => 200,
            ]);
        }
        /*if($result){
            $this->success('文件上传成功，数据已经导入！');
        }else{
            // 上传失败获取错误信息
            $this->error($file->getError());
        }*/
    }

    /*
    * CSV导出
    */
    public static function downCsv()
    {
        $csv = new Csv();  //实例化后才可以调用之前类文件定义好的方法
        $list = Db::name('device')->field('sn,imei')->select();
        $csv_title = array('sn','imei');
        $csv->put_csv($list,$csv_title);
    }

    //导入Excel
    /*public static function intoExcel()
    {
        // 获取表单上传文件
        $file = request()->file('file');

        //dump($_FILES);exit;
        if(empty($file)) {
            return json([
                'code' => 4001,
            ]);
        }
        if (!empty ($_FILES['name'])) {
            $tmp_file = $_FILES['tmp_name'];
            $file_types = explode(".", $_FILES['name']);
            $file_type = $file_types [count($file_types) - 1];
            /*判别是不是.xls文件，判别是不是excel文件
            if (strtolower($file_type) != "xlsx") {
                return json([
                    'code' => 4002,
                ]);
            }
            /*设置上传路径
            /*百度有些文章写的上传路径经过编译之后斜杠不对。不对的时候用大写的DS代替，然后用连接符链接就可以拼凑路径了。
            $savePath = ROOT_PATH . 'public' . DS . 'uploads' . DS;/*以时间来命名上传的文件
            $str = date('Ymdhis');
            $file_name = $str . "." . $file_type;

            /*是否上传成功

            if (!copy($tmp_file, $savePath . $file_name)) {
                return json([
                    'code' => 4003,
                ]);
            }
            /*
            *对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中
            *注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入

            require THINK_PATH.'library/think/ExcelToArrary.php';//导入excelToArray类
            //引入这个类试了百度出来的好几个方法都不行。最后简单粗暴的使用了require方式。这个类想放在哪里放在哪里。只要路径对就行。

            $ExcelToArrary = new ExcelToArrary();//实例化

            $res=$ExcelToArrary->read($savePath.$file_name,"UTF-8",$file_type);//传参,判断office2007还是office2003

            /*对生成的数组进行数据库的写入
            foreach ($res as $k => $v) {
                if ($k > 1) {
                    $data[$k]['sn'] = $v[0];
                    $data[$k]['imei'] = $v[1];
                }
            }
            //插入的操作最好放在循环外面
            $result = model('Device')->insertAll($data);
            if($result){
                return json([
                    'code' => 200,
                ]);
            }
        }
    }*/
}